<!DOCTYPE html>
<html lang="en-US">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>MySQL主从复制 | HelloBin</title>
    <meta name="generator" content="VuePress 1.8.2">
    <link rel="icon" href="/blog/favicon.ico">
    <meta name="description" content="偷得浮生半日闲,人间至味是清欢">
    <meta name="viewport" content="width=device-width,initial-scale=1,user-scalable=no">
    
    <link rel="preload" href="/blog/assets/css/0.styles.e16238e2.css" as="style"><link rel="preload" href="/blog/assets/js/app.eeaaf521.js" as="script"><link rel="preload" href="/blog/assets/js/3.f4973d30.js" as="script"><link rel="preload" href="/blog/assets/js/1.c6353690.js" as="script"><link rel="preload" href="/blog/assets/js/16.0a4356ee.js" as="script"><link rel="prefetch" href="/blog/assets/js/10.472afe8d.js"><link rel="prefetch" href="/blog/assets/js/11.e226b339.js"><link rel="prefetch" href="/blog/assets/js/12.db3de697.js"><link rel="prefetch" href="/blog/assets/js/13.ab84a112.js"><link rel="prefetch" href="/blog/assets/js/14.3472d173.js"><link rel="prefetch" href="/blog/assets/js/15.3a0e6f2f.js"><link rel="prefetch" href="/blog/assets/js/17.b1429b08.js"><link rel="prefetch" href="/blog/assets/js/18.fc44351d.js"><link rel="prefetch" href="/blog/assets/js/4.027713aa.js"><link rel="prefetch" href="/blog/assets/js/5.72722b36.js"><link rel="prefetch" href="/blog/assets/js/6.1064904d.js"><link rel="prefetch" href="/blog/assets/js/7.baf14459.js"><link rel="prefetch" href="/blog/assets/js/8.b7d110a6.js"><link rel="prefetch" href="/blog/assets/js/9.e4886eea.js">
    <link rel="stylesheet" href="/blog/assets/css/0.styles.e16238e2.css">
  </head>
  <body>
    <div id="app" data-server-rendered="true"><div class="theme-container no-sidebar" data-v-1aefc0b4><div data-v-1aefc0b4><div id="loader-wrapper" class="loading-wrapper" data-v-d48f4d20 data-v-1aefc0b4 data-v-1aefc0b4><div class="loader-main" data-v-d48f4d20><div data-v-d48f4d20></div><div data-v-d48f4d20></div><div data-v-d48f4d20></div><div data-v-d48f4d20></div></div> <!----> <!----></div> <div class="password-shadow password-wrapper-out" style="display:none;" data-v-25ba6db2 data-v-1aefc0b4 data-v-1aefc0b4><h3 class="title" data-v-25ba6db2 data-v-25ba6db2>HelloBin</h3> <p class="description" data-v-25ba6db2 data-v-25ba6db2>偷得浮生半日闲,人间至味是清欢</p> <label id="box" class="inputBox" data-v-25ba6db2 data-v-25ba6db2><input type="password" value="" data-v-25ba6db2> <span data-v-25ba6db2>Konck! Knock!</span> <button data-v-25ba6db2>OK</button></label> <div class="footer" data-v-25ba6db2 data-v-25ba6db2><span data-v-25ba6db2><i class="iconfont reco-theme" data-v-25ba6db2></i> <a target="blank" href="https://vuepress-theme-reco.recoluan.com" data-v-25ba6db2>vuePress-theme-reco</a></span> <span data-v-25ba6db2><i class="iconfont reco-copyright" data-v-25ba6db2></i> <a data-v-25ba6db2><!---->
            
          <!---->
          2022
        </a></span></div></div> <div class="hide" data-v-1aefc0b4><header class="navbar" data-v-1aefc0b4><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/blog/" class="home-link router-link-active"><img src="/blog/logo.png" alt="HelloBin" class="logo"> <span class="site-name">HelloBin</span></a> <div class="links"><div class="color-picker"><a class="color-button"><i class="iconfont reco-color"></i></a> <div class="color-picker-menu" style="display:none;"><div class="mode-options"><h4 class="title">Choose mode</h4> <ul class="color-mode-options"><li class="dark">dark</li><li class="auto active">auto</li><li class="light">light</li></ul></div></div></div> <div class="search-box"><i class="iconfont reco-search"></i> <input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/blog/" class="nav-link"><i class="iconfont reco-home"></i>
  主页
</a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-category"></i>
      学习笔记
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/blog/categories/Golang/" class="nav-link"><i class="undefined"></i>
  Golang
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/MySQL/" class="nav-link"><i class="undefined"></i>
  MySQL
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/Linux/" class="nav-link"><i class="undefined"></i>
  Linux
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/Nginx/" class="nav-link"><i class="undefined"></i>
  Nginx
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/Redis/" class="nav-link"><i class="undefined"></i>
  Redis
</a></li></ul></div></div><div class="nav-item"><a href="/blog/tag/" class="nav-link"><i class="iconfont reco-tag"></i>
  Tag
</a></div> <!----></nav></div></header> <div class="sidebar-mask" data-v-1aefc0b4></div> <aside class="sidebar" data-v-1aefc0b4><div class="personal-info-wrapper" data-v-39576ba9 data-v-1aefc0b4><img src="/blog/avatar.png" alt="author-avatar" class="personal-img" data-v-39576ba9> <!----> <div class="num" data-v-39576ba9><div data-v-39576ba9><h3 data-v-39576ba9>8</h3> <h6 data-v-39576ba9>Articles</h6></div> <div data-v-39576ba9><h3 data-v-39576ba9>9</h3> <h6 data-v-39576ba9>Tags</h6></div></div> <ul class="social-links" data-v-39576ba9></ul> <hr data-v-39576ba9></div> <nav class="nav-links"><div class="nav-item"><a href="/blog/" class="nav-link"><i class="iconfont reco-home"></i>
  主页
</a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-category"></i>
      学习笔记
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/blog/categories/Golang/" class="nav-link"><i class="undefined"></i>
  Golang
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/MySQL/" class="nav-link"><i class="undefined"></i>
  MySQL
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/Linux/" class="nav-link"><i class="undefined"></i>
  Linux
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/Nginx/" class="nav-link"><i class="undefined"></i>
  Nginx
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/Redis/" class="nav-link"><i class="undefined"></i>
  Redis
</a></li></ul></div></div><div class="nav-item"><a href="/blog/tag/" class="nav-link"><i class="iconfont reco-tag"></i>
  Tag
</a></div> <!----></nav> <!----> </aside> <div class="password-shadow password-wrapper-in" style="display:none;" data-v-25ba6db2 data-v-1aefc0b4><h3 class="title" data-v-25ba6db2 data-v-25ba6db2>MySQL主从复制</h3> <!----> <label id="box" class="inputBox" data-v-25ba6db2 data-v-25ba6db2><input type="password" value="" data-v-25ba6db2> <span data-v-25ba6db2>Konck! Knock!</span> <button data-v-25ba6db2>OK</button></label> <div class="footer" data-v-25ba6db2 data-v-25ba6db2><span data-v-25ba6db2><i class="iconfont reco-theme" data-v-25ba6db2></i> <a target="blank" href="https://vuepress-theme-reco.recoluan.com" data-v-25ba6db2>vuePress-theme-reco</a></span> <span data-v-25ba6db2><i class="iconfont reco-copyright" data-v-25ba6db2></i> <a data-v-25ba6db2><!---->
            
          <!---->
          2022
        </a></span></div></div> <div data-v-1aefc0b4><main class="page"><section><div class="page-title"><h1 class="title">MySQL主从复制</h1> <div data-v-f875f3fc><!----> <i class="iconfont reco-date" data-v-f875f3fc><span data-v-f875f3fc>3/20/2022</span></i> <!----> <i class="tags iconfont reco-tag" data-v-f875f3fc><span class="tag-item" data-v-f875f3fc>MySQL</span></i></div></div> <div class="theme-reco-content content__default"><h3 id="_1、使用技术"><a href="#_1、使用技术" class="header-anchor">#</a> 1、使用技术</h3> <ul><li>mysql数据库（8.0.19）</li> <li>ubuntu（18.04）</li> <li>centos(7.5)</li></ul> <h3 id="_2、技术前提"><a href="#_2、技术前提" class="header-anchor">#</a> 2、技术前提</h3> <ul><li>确保2台Linux主机（本文提供如下参考）
<ul><li>master节点：
<ul><li>地址：192.168.0.13</li> <li>端口：3306</li> <li>账号密码：remote / abc123</li></ul></li> <li>slave节点：
<ul><li>地址：192.168.19.14</li> <li>端口：3306</li> <li>账号密码：remote / abc123</li></ul></li></ul></li> <li>确保主机能够互相访问</li></ul> <h3 id="_3、配置"><a href="#_3、配置" class="header-anchor">#</a> 3、配置</h3> <ul><li>设置主机（ip 为从机ip）</li> <li>在master节点，使用linux命令编辑mysqld.cnf文件</li></ul> <div class="language- line-numbers-mode"><pre class="language-text"><code>    vi /etc/mysql/mysql.conf.d/mysqld.cnf
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>（核心配置）在[mysqld]节点下添加如下内容（在master配置，slave不需要）</li></ul> <div class="language-xml line-numbers-mode"><pre class="language-xml"><code>    # 主机ID（一般都根据自己的ip地址而定）
    server-id=1
    # 自己希望同步的数据库，用逗号隔开
    binlog-do-db=test
    # 希望忽略的数据库（一般mysql数据库都不需要同步）
    binlog-ignore-db=mysql
    # 定义binlog的名称（最好是有些含义）
    log-bin=binlog
    # 定义binlog的缓存大小
    binlog_cache_size=1M 
    # 定义binlog的格式（mixed,statement,row，默认格式是 statement）
    binlog_format=mixed
    # binlog文件存在的时间为7天（如果是0则表示不删除）
    expire_logs_days=7
    # 忽略主从复制出现的异常吗（1062代表主键重复异常）
    slave_skip_errors=1062
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br></div></div><ul><li>重启mysql服务</li></ul> <div class="language-xml line-numbers-mode"><pre class="language-xml"><code>    service mysql restart
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>创建一个用户</li></ul> <div class="language- line-numbers-mode"><pre class="language-text"><code>CREATE USER 'slave_user'@'192.168.0.14' IDENTIFIED WITH mysql_native_password BY 'abc123';
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>授权从机可以使用remote用户复制主机</li></ul> <div class="language- line-numbers-mode"><pre class="language-text"><code>GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'192.168.0.14';
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>刷新权限</li></ul> <div class="language- line-numbers-mode"><pre class="language-text"><code>flush privileges;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>查看master节点状态</li></ul> <div class="language- line-numbers-mode"><pre class="language-text"><code>show master status;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>会出现类似的内容：（注意：请记住：File和Position的值，下面步骤会用到）</li></ul> <div class="language-xml line-numbers-mode"><pre class="language-xml"><code>    +-----------------------+----------+-----------------+-----------------------+-------------------+
    | File                  | Position | Binlog_Do_DB    | Binlog_Ignore_DB      | Executed_Gtid_Set |
    +-----------------------+----------+-----------------+-----------------------+-------------------+
    | binlog.000042 |      400 | test         | mysql                 |                   |
    +-----------------------+----------+-----------------+-----------------------+-------------------+
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><ul><li><p>设置从机</p></li> <li><p>（核心配置）登录slave节点，编辑/etc/mysql/mysql.conf.d/mysqld.cnf内容如下（slave配置，master不需要）</p></li></ul> <div class="language- line-numbers-mode"><pre class="language-text"><code>    server-id=2
    binlog-ignore-db=mysql 
    log-bin=jmin-mysql-slave1-bin 
    binlog_cache_size=1M
    binlog_format=mixed
    expire_logs_days=7
    slave_skip_errors=1062 
    # 中继log文件（slave会把master的log先读入中继log，然后才从中继log写入库中）
    relay_log=jmin-mysql-relay-bin 
    # 表示 slave 将复制事件写进自己的二进制日志
    log_slave_updates=1
    # 防止改变数据(除了特殊的线程)
    read_only=1
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br></div></div><ul><li>重启mysql服务</li></ul> <div class="language- line-numbers-mode"><pre class="language-text"><code>    service mysql restart
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><div class="language- line-numbers-mode"><pre class="language-text"><code>设置从机（IP为主机ip）

CHANGE MASTER TO
MASTER_HOST='192.168.0.13',
MASTER_USER='remote',
MASTER_PASSWORD='abc123',
MASTER_LOG_FILE='binlog.000042',
MASTER_LOG_POS=400;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div><ul><li>启动slave节点</li></ul> <div class="language- line-numbers-mode"><pre class="language-text"><code>    start slave;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>查看一下slave同步的状态</li></ul> <div class="language- line-numbers-mode"><pre class="language-text"><code>    show slave status\G;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>如果出现以下内容，就说明成功</li></ul> <div class="language- line-numbers-mode"><pre class="language-text"><code>    Slave_IO_Running:Yes
    Slave_SQL_Running:Yes
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><ul><li>最后可以验证一下，在master节点新增一条数据，查看slave节点有没有同步数据</li></ul></div></section> <footer class="page-edit"><!----> <div class="last-updated"><span class="prefix">Last Updated: </span> <span class="time">3/25/2022, 9:54:21 PM</span></div></footer> <!----> <div class="comments-wrapper"><!----></div> <ul class="side-bar sub-sidebar-wrapper" style="width:12rem;" data-v-cb1513f6><li class="level-3" data-v-cb1513f6><a href="/blog/blogs/MySQL/2022032508.html#_1、使用技术" class="sidebar-link reco-side-_1、使用技术" data-v-cb1513f6>1、使用技术</a></li><li class="level-3" data-v-cb1513f6><a href="/blog/blogs/MySQL/2022032508.html#_2、技术前提" class="sidebar-link reco-side-_2、技术前提" data-v-cb1513f6>2、技术前提</a></li><li class="level-3" data-v-cb1513f6><a href="/blog/blogs/MySQL/2022032508.html#_3、配置" class="sidebar-link reco-side-_3、配置" data-v-cb1513f6>3、配置</a></li></ul></main> <!----></div></div></div></div><div class="global-ui"><div class="back-to-ceiling" style="right:1rem;bottom:6rem;width:2.5rem;height:2.5rem;border-radius:.25rem;line-height:2.5rem;display:none;" data-v-c6073ba8 data-v-c6073ba8><svg t="1574745035067" viewBox="0 0 1024 1024" version="1.1" xmlns="http://www.w3.org/2000/svg" p-id="5404" class="icon" data-v-c6073ba8><path d="M526.60727968 10.90185116a27.675 27.675 0 0 0-29.21455937 0c-131.36607665 82.28402758-218.69155461 228.01873535-218.69155402 394.07834331a462.20625001 462.20625001 0 0 0 5.36959153 69.94390903c1.00431239 6.55289093-0.34802892 13.13561351-3.76865779 18.80351572-32.63518765 54.11355614-51.75690182 118.55860487-51.7569018 187.94566865a371.06718723 371.06718723 0 0 0 11.50484808 91.98906777c6.53300375 25.50556257 41.68394495 28.14064038 52.69160883 4.22606766 17.37162448-37.73630017 42.14135425-72.50938081 72.80769204-103.21549295 2.18761121 3.04276886 4.15646224 6.24463696 6.40373557 9.22774369a1871.4375 1871.4375 0 0 0 140.04691725 5.34970492 1866.36093723 1866.36093723 0 0 0 140.04691723-5.34970492c2.24727335-2.98310674 4.21612437-6.18497483 6.3937923-9.2178004 30.66633723 30.70611158 55.4360664 65.4791928 72.80769147 103.21549355 11.00766384 23.91457269 46.15860503 21.27949489 52.69160879-4.22606768a371.15156223 371.15156223 0 0 0 11.514792-91.99901164c0-69.36717486-19.13165746-133.82216804-51.75690182-187.92578088-3.42062944-5.66790279-4.76302748-12.26056868-3.76865837-18.80351632a462.20625001 462.20625001 0 0 0 5.36959269-69.943909c-0.00994388-166.08943902-87.32547796-311.81420293-218.6915546-394.09823051zM605.93803103 357.87693858a93.93749974 93.93749974 0 1 1-187.89594924 6.1e-7 93.93749974 93.93749974 0 0 1 187.89594924-6.1e-7z" p-id="5405" data-v-c6073ba8></path><path d="M429.50777625 765.63860547C429.50777625 803.39355007 466.44236686 1000.39046097 512.00932183 1000.39046097c45.56695499 0 82.4922232-197.00623328 82.5015456-234.7518555 0-37.75494459-36.9345906-68.35043303-82.4922232-68.34111062-45.57627738-0.00932239-82.52019037 30.59548842-82.51086798 68.34111062z" p-id="5406" data-v-c6073ba8></path></svg></div></div></div>
    <script src="/blog/assets/js/app.eeaaf521.js" defer></script><script src="/blog/assets/js/3.f4973d30.js" defer></script><script src="/blog/assets/js/1.c6353690.js" defer></script><script src="/blog/assets/js/16.0a4356ee.js" defer></script>
  </body>
</html>
